/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Handlers;

import gui.Main;
import ObjectClass.SQLCommunication;
import ObjectClass.Task;
import ObjectClass.Vehicle;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Jeppe
 */
public class VehicleHandler {

    private SQLCommunication sqlc;
    private ArrayList<Vehicle> vehicleList;
    private TaskHandler taskHandler;
    private Main main;
    private final static String TABLE = "Vehicle";

    public VehicleHandler(Main main) {
        this.main = main;
        this.sqlc = main.getSql();
        vehicleList = new ArrayList<>();
        this.taskHandler = main.getTaskHandler();
    }

    private void instantierVehicleList() {
        int ID;
        String licensePlate;
        String vType;
        int maxNumberOfPallets;
        int cubicMeters;
        int maxWeight;

        String sql = "SELECT * FROM " + TABLE;

        ResultSet rs = sqlc.select(TABLE, sql);
        try {
            while (rs.next()) {
                ID = rs.getInt("ID");
                licensePlate = rs.getString("Licenseplate");
                vType = rs.getString("VType");
                maxNumberOfPallets = rs.getInt("MaxNumberOfPallets");
                cubicMeters = rs.getInt("CubicMeters");
                maxWeight = rs.getInt("MaxWeight");

                Vehicle vehicle = new Vehicle(ID, licensePlate, vType, maxNumberOfPallets, cubicMeters, maxWeight);
                vehicleList.add(vehicle);
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af vehicleList");
        }
    }

    public ArrayList<Vehicle> getVehicleListByAll(int cargoNumber, int cargoWeight, String cargoType, int dd, int mm, int YYYY) {
        ArrayList<Vehicle> demandVehicleList = new ArrayList<>();
        int ID;
        String licensePlate;
        String vType;
        int maxNumberOfPallets;
        int maxCubicMeters;
        int maxWeight;
        System.out.println(" CargoType: " + cargoType + " CargoNumber: " + cargoNumber + " Weight: " + cargoWeight);
        String sql = "SELECT * FROM " + TABLE;

        if (cargoType.equalsIgnoreCase("pallet")) {
            sql = "SELECT  * "
                    + "FROM  vehicle "
                    + "WHERE vehicle.LicensePlate NOT IN (SELECT task.VehicleLicense "
                    + "FROM task "
                    + "WHERE task.DD = " + dd + " AND task.MM = " + mm + " AND task.YYYY = " + YYYY + ") "
                    + "AND vehicle.LicensePlate IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.MaxWeight >= " + cargoWeight + ") "
                    + "AND vehicle.LicensePlate IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.MaxNumberOfPallets >= " + cargoNumber + ") "
                    + "AND vehicle.LicensePlate NOT IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.VType like '%kran%')";

        } else if (cargoType.equalsIgnoreCase("cargo")) {
            sql = "SELECT  * "
                    + "FROM  vehicle "
                    + "WHERE vehicle.LicensePlate NOT IN (SELECT task.VehicleLicense "
                    + "FROM task "
                    + "WHERE task.DD = " + dd + " AND task.MM = " + mm + " AND task.YYYY = " + YYYY + ") "
                    + "AND vehicle.LicensePlate IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.MaxWeight >= " + cargoWeight + ") "
                    + "AND vehicle.LicensePlate IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.CubicMeters >= " + cargoNumber + ") "
                    + "AND vehicle.LicensePlate NOT IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.VType like '%kran%')";


        } else if (cargoType.equalsIgnoreCase("Special")) {
            sql = "SELECT  * "
                    + "FROM  vehicle "
                    + "WHERE vehicle.LicensePlate NOT IN (SELECT task.VehicleLicense "
                    + "FROM task "
                    + "WHERE task.DD = " + dd + " AND task.MM = " + mm + " AND task.YYYY = " + YYYY + ") "
                    + "AND vehicle.LicensePlate IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.MaxWeight >= " + cargoWeight + ") "
                    + "AND vehicle.LicensePlate IN (SELECT LicensePlate "
                    + "FROM vehicle "
                    + "WHERE vehicle.VType like '%kran%')";
        }

        ResultSet rs = sqlc.select(TABLE, sql);
        try {
            while (rs.next()) {
                ID = rs.getInt("ID");
                licensePlate = rs.getString("Licenseplate");
                vType = rs.getString("VType");
                maxNumberOfPallets = rs.getInt("MaxNumberOfPallets");
                maxCubicMeters = rs.getInt("CubicMeters");
                maxWeight = rs.getInt("MaxWeight");

                Vehicle vehicle = new Vehicle(ID, licensePlate, vType, maxNumberOfPallets, maxCubicMeters, maxWeight);
                demandVehicleList.add(vehicle);
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af vehicleList");
        }        
        return demandVehicleList;
    }

    public ArrayList<Vehicle> getVehicleListByType(String vType) {
        ArrayList<Vehicle> demandVehicleList = new ArrayList<>();

        for (int i = 0; i < vehicleList.size(); i++) {
            if (vehicleList.get(i).getvType().equals(vType)) {
                demandVehicleList.add(vehicleList.get(i));
            }
        }
        return demandVehicleList;
    }

    /**
     * Brug ikke om nødvendigt
     *
     * @param licensePlate
     * @return
     */
    public Vehicle getVehicleByLicensePlate(String licensePlate) {
        Vehicle vehicle = null;
        for (int i = 0; i < vehicleList.size(); i++) {
            if (vehicleList.get(i).getLicensePlate().equals(licensePlate)) {
                vehicle = vehicleList.get(i);
            }
        }
        return vehicle;
    }

    public boolean tjekVehicle(Vehicle etVehicle) {
        boolean exists = false;

        for (int i = 0; i < vehicleList.size(); i++) {
            if (vehicleList.get(i).getLicensePlate().equals(etVehicle.getLicensePlate())) {
                exists = true;
            }
        }
        return exists;
    }

//    public boolean tjekVehicleByDato(int dd, int mm, int YYYY, Vehicle aVehicle) {
//        boolean exists = false;
//        exists = taskHandler.tjekTaskByDatoANDVehicle(dd, mm, YYYY, aVehicle);
//        return exists;
//    }

    /**
     * Indsætter et vehicle i databasen.
     *
     * @param etVehicle
     */
    public void insertToDB(Vehicle etVehicle) {
        String sql;
        sql = "insert into " + TABLE + " (licensePlate, vType, MaxNumberOfPallets, CubicMeters, Weight) values " + etVehicle.toStringSQL();
        if (tjekVehicle(etVehicle)) {
            System.out.println("Der findes allerede et vehicle med de oplysninger");
        } else {
            sqlc.insert(TABLE, sql);
            System.out.println("Vehicle er tilføjet");
            instantierVehicleList();
        }
    }

    /**
     * Eksekverer denne sql-streng: SQL = "update "+table+" set "+coloumnlabel+
     * "= "+newString+" where tlfnr="+tlfnr
     *
     * @param table
     * @param coloumnlabel
     * @param newString
     * @param tlfnr
     */
    public void updateString(String coloumnlabel, String newString, String Titel) {
        String sql;
        sql = "update " + TABLE + " set " + coloumnlabel + "= '" + newString + "' where Titel=" + Titel;
        sqlc.update(TABLE, sql);
    }

    /**
     * !!!!!!!!!!!!!!!!!!!!!!!!!!!! Funktion der tjekker om et vehicle
     * eksisterer i databasen. Hvis det er tilfældet sletter metoden det
     * pågældende vehicle fra databasen
     */
    public void delete(Vehicle etVehicle) {
        String sql;
        sql = "delete from " + TABLE + " where Licenseplate=('" + etVehicle.getLicensePlate() + "')";
        if (tjekVehicle(etVehicle)) {
            sqlc.delete(TABLE, sql);
        }
    }
}
